
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 06/10/2015 14:15:01
-- Generated from EDMX file: D:\github\XwangWeb\XwangTask\Xwang.Task.Model\TaskModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [TaskDb];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_DepInfoUserInfo]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[sys_Users] DROP CONSTRAINT [FK_DepInfoUserInfo];
GO
IF OBJECT_ID(N'[dbo].[FK_UserInfoTask]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[t_Tasks] DROP CONSTRAINT [FK_UserInfoTask];
GO
IF OBJECT_ID(N'[dbo].[FK_TaskTaskJoinUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[t_TaskJoinUser] DROP CONSTRAINT [FK_TaskTaskJoinUser];
GO
IF OBJECT_ID(N'[dbo].[FK_UserInfoTaskJoinUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[t_TaskJoinUser] DROP CONSTRAINT [FK_UserInfoTaskJoinUser];
GO
IF OBJECT_ID(N'[dbo].[FK_UserInfoTaskFeedback]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[t_TaskFeedback] DROP CONSTRAINT [FK_UserInfoTaskFeedback];
GO
IF OBJECT_ID(N'[dbo].[FK_TaskTaskFeedback]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[t_TaskFeedback] DROP CONSTRAINT [FK_TaskTaskFeedback];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[t_Tasks]', 'U') IS NOT NULL
    DROP TABLE [dbo].[t_Tasks];
GO
IF OBJECT_ID(N'[dbo].[sys_Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[sys_Users];
GO
IF OBJECT_ID(N'[dbo].[sys_Department]', 'U') IS NOT NULL
    DROP TABLE [dbo].[sys_Department];
GO
IF OBJECT_ID(N'[dbo].[t_TaskJoinUser]', 'U') IS NOT NULL
    DROP TABLE [dbo].[t_TaskJoinUser];
GO
IF OBJECT_ID(N'[dbo].[t_TaskFeedback]', 'U') IS NOT NULL
    DROP TABLE [dbo].[t_TaskFeedback];
GO
IF OBJECT_ID(N'[dbo].[t_NewsInfo]', 'U') IS NOT NULL
    DROP TABLE [dbo].[t_NewsInfo];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 't_Tasks'
CREATE TABLE [dbo].[t_Tasks] (
    [KeyId] int IDENTITY(1,1) NOT NULL,
    [TaskTitle] nvarchar(200)  NOT NULL,
    [TaskDesc] nvarchar(max)  NULL,
    [Biller] int  NOT NULL,
    [TaskProperty] int  NULL,
    [TaskStatus] int  NOT NULL,
    [BillDate] datetime  NOT NULL,
    [TaskBeginTime] datetime  NULL,
    [TaskEndTime] datetime  NULL,
    [TaskRealEndTime] datetime  NULL,
    [TaskListKind] int  NULL
);
GO

-- Creating table 'sys_Users'
CREATE TABLE [dbo].[sys_Users] (
    [KeyId] int IDENTITY(1,1) NOT NULL,
    [UserName] nvarchar(max)  NOT NULL,
    [LoginId] nvarchar(max)  NOT NULL,
    [Pwd] nvarchar(max)  NOT NULL,
    [DepId] int  NOT NULL
);
GO

-- Creating table 'sys_Department'
CREATE TABLE [dbo].[sys_Department] (
    [KeyId] int IDENTITY(1,1) NOT NULL,
    [DepName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 't_TaskJoinUser'
CREATE TABLE [dbo].[t_TaskJoinUser] (
    [KeyId] int IDENTITY(1,1) NOT NULL,
    [TaskId] int  NOT NULL,
    [UserId] int  NOT NULL,
    [Status] int  NOT NULL
);
GO

-- Creating table 't_TaskFeedback'
CREATE TABLE [dbo].[t_TaskFeedback] (
    [KeyId] int IDENTITY(1,1) NOT NULL,
    [TaskId] int  NOT NULL,
    [UserId] int  NOT NULL,
    [FContent] nvarchar(max)  NOT NULL,
    [FeedbackTime] datetime  NOT NULL,
    [FType] int  NOT NULL
);
GO

-- Creating table 't_NewsInfo'
CREATE TABLE [dbo].[t_NewsInfo] (
    [KeyId] int IDENTITY(1,1) NOT NULL,
    [UserId] int  NOT NULL,
    [TaskId] int  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [CreateTime] datetime  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [KeyId] in table 't_Tasks'
ALTER TABLE [dbo].[t_Tasks]
ADD CONSTRAINT [PK_t_Tasks]
    PRIMARY KEY CLUSTERED ([KeyId] ASC);
GO

-- Creating primary key on [KeyId] in table 'sys_Users'
ALTER TABLE [dbo].[sys_Users]
ADD CONSTRAINT [PK_sys_Users]
    PRIMARY KEY CLUSTERED ([KeyId] ASC);
GO

-- Creating primary key on [KeyId] in table 'sys_Department'
ALTER TABLE [dbo].[sys_Department]
ADD CONSTRAINT [PK_sys_Department]
    PRIMARY KEY CLUSTERED ([KeyId] ASC);
GO

-- Creating primary key on [KeyId] in table 't_TaskJoinUser'
ALTER TABLE [dbo].[t_TaskJoinUser]
ADD CONSTRAINT [PK_t_TaskJoinUser]
    PRIMARY KEY CLUSTERED ([KeyId] ASC);
GO

-- Creating primary key on [KeyId] in table 't_TaskFeedback'
ALTER TABLE [dbo].[t_TaskFeedback]
ADD CONSTRAINT [PK_t_TaskFeedback]
    PRIMARY KEY CLUSTERED ([KeyId] ASC);
GO

-- Creating primary key on [KeyId] in table 't_NewsInfo'
ALTER TABLE [dbo].[t_NewsInfo]
ADD CONSTRAINT [PK_t_NewsInfo]
    PRIMARY KEY CLUSTERED ([KeyId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [DepId] in table 'sys_Users'
ALTER TABLE [dbo].[sys_Users]
ADD CONSTRAINT [FK_DepInfoUserInfo]
    FOREIGN KEY ([DepId])
    REFERENCES [dbo].[sys_Department]
        ([KeyId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_DepInfoUserInfo'
CREATE INDEX [IX_FK_DepInfoUserInfo]
ON [dbo].[sys_Users]
    ([DepId]);
GO

-- Creating foreign key on [Biller] in table 't_Tasks'
ALTER TABLE [dbo].[t_Tasks]
ADD CONSTRAINT [FK_UserInfoTask]
    FOREIGN KEY ([Biller])
    REFERENCES [dbo].[sys_Users]
        ([KeyId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_UserInfoTask'
CREATE INDEX [IX_FK_UserInfoTask]
ON [dbo].[t_Tasks]
    ([Biller]);
GO

-- Creating foreign key on [TaskId] in table 't_TaskJoinUser'
ALTER TABLE [dbo].[t_TaskJoinUser]
ADD CONSTRAINT [FK_TaskTaskJoinUser]
    FOREIGN KEY ([TaskId])
    REFERENCES [dbo].[t_Tasks]
        ([KeyId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskTaskJoinUser'
CREATE INDEX [IX_FK_TaskTaskJoinUser]
ON [dbo].[t_TaskJoinUser]
    ([TaskId]);
GO

-- Creating foreign key on [UserId] in table 't_TaskJoinUser'
ALTER TABLE [dbo].[t_TaskJoinUser]
ADD CONSTRAINT [FK_UserInfoTaskJoinUser]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[sys_Users]
        ([KeyId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_UserInfoTaskJoinUser'
CREATE INDEX [IX_FK_UserInfoTaskJoinUser]
ON [dbo].[t_TaskJoinUser]
    ([UserId]);
GO

-- Creating foreign key on [UserId] in table 't_TaskFeedback'
ALTER TABLE [dbo].[t_TaskFeedback]
ADD CONSTRAINT [FK_UserInfoTaskFeedback]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[sys_Users]
        ([KeyId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_UserInfoTaskFeedback'
CREATE INDEX [IX_FK_UserInfoTaskFeedback]
ON [dbo].[t_TaskFeedback]
    ([UserId]);
GO

-- Creating foreign key on [TaskId] in table 't_TaskFeedback'
ALTER TABLE [dbo].[t_TaskFeedback]
ADD CONSTRAINT [FK_TaskTaskFeedback]
    FOREIGN KEY ([TaskId])
    REFERENCES [dbo].[t_Tasks]
        ([KeyId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskTaskFeedback'
CREATE INDEX [IX_FK_TaskTaskFeedback]
ON [dbo].[t_TaskFeedback]
    ([TaskId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------